package homework_5;

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class DBHelper {
	 private static final String url = "jdbc:mysql://localhost:3306/Bilibili?autoReconnect=true&useSSL=false";  
	 private static final String name = "com.mysql.cj.jdbc.Driver";  
	 private static final String user = "root";  
	 private static final String password = "ljy123456";  
	  
	 private Connection conn = null;  
	 private PreparedStatement pst = null;  
	  
	 public DBHelper() {  
	    try {  
	        Class.forName(name);//指定连接类型  
	        conn = DriverManager.getConnection(url, user, password);//获取连接  
	    } catch (Exception e) {  
	        e.printStackTrace();  
	    }  
	    System.out.println("连接成功！");
	 }  
	 // Create a table for each category with its ID
	 public void createTable(int cateId) {
		 String sql = "create table category" + cateId + " (" +
				 "aid varchar(20) not null,"  +
				 "author varchar(50) not null," +
				 "category varchar(10) not null," +
				 "typeName varchar(20) not null," +
				 "title varchar(100) not null," + 
				 "favorite int not null," + 
				 "coins int not null" +
				 ")";
		 try {
			pst = conn.prepareStatement(sql);
			pst.executeUpdate();
		 } catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("建表失败！");
			e.printStackTrace();
			System.exit(1);
		 }
		 System.out.println("建表成功！");
	 }
	 
	 public void insertModel(VideoModel model) {
		 String sql = "insert into category" + model.cateId + 
					"(aid, author, category, typeName, title, favorite, coins)" +
					"values" +
					"(\"" + model.aid +"\", \"" +
					model.author + "\", \"" +
					model.category + "\", \"" +
					model.typeName + "\", \"" +
					model.title + "\", " +
					model.favorite + ", " +
					model.coins + ")";
		 try {
				pst = conn.prepareStatement(sql);
				pst.executeUpdate();
		 } catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("插入数据库时出错");
				e.printStackTrace();
		 }
	 }
	 
	 public void queryData(int cateId) {
		 String sql = "select * from category" + cateId;
		 try {
				pst = conn.prepareStatement(sql);
				ResultSet resultSet = pst.executeQuery();
				System.out.println(resultSet);
		 } catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("数据库查询失败");
				e.printStackTrace();
		 }
	 }
	 // get top N data
	 public ArrayList<VideoModel> obtainTopModels(int cateId, int topNum) {
		 String sql = "select * from category" + cateId +
				 " order by favorite desc" +
				 " limit " + topNum;
		 ArrayList<VideoModel> tops = new ArrayList<VideoModel>();
		 try {
				pst = conn.prepareStatement(sql);
				ResultSet resultSet = pst.executeQuery();
				while (resultSet.next()) {
					VideoModel model = new VideoModel();
					model.aid = resultSet.getString("aid");
					model.author = resultSet.getString("author");
					model.typeName = resultSet.getString("typeName");
					model.title = resultSet.getString("title");
					model.category = resultSet.getString("category");
					model.favorite = Integer.parseInt(resultSet.getString("favorite"));
					model.coins = Integer.parseInt(resultSet.getString("coins"));
					tops.add(model);
				}
		 } catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("数据库查询失败");
				e.printStackTrace();
		 }
		 return tops;
		
	}
	 
	 public void close() {  
	    try {  
	        this.conn.close();  
	        this.pst.close();  
	    } catch (SQLException e) {  
	        e.printStackTrace();  
	    }  
	}  	    
}
